package com.live.shop.sql;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.live.shop.model.ProductGroupModel;
import com.live.shop.model.ProductMaterialRecordModel;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ProductGroupListDatabase {
	private static MyDatabaseHelper helper;
	public ProductGroupListDatabase(Context context){
		//创建数据库和表
		helper = new MyDatabaseHelper(context, "user.db",null,3,null);
	}

	//查询所有
	@SuppressLint("Range")
	public static List<ProductGroupModel> selectAll() {
		List<ProductGroupModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor1 = database.rawQuery("select * from product_group_list ", null);
		if (cursor1 != null && cursor1.getCount() > 0) {
			while (cursor1.moveToNext()) {
				ProductGroupModel model = new ProductGroupModel();
				int product_group_id = cursor1.getInt(cursor1.getColumnIndex("product_group_id"));
				model.setProductGroupId(product_group_id);
				model.setProductGroupName(cursor1.getString(cursor1.getColumnIndex("product_group_name")));
				model.setProductGroupStatus(cursor1.getInt(cursor1.getColumnIndex("product_group_status")));
				model.setCreateTime(cursor1.getString(cursor1.getColumnIndex("create_time")));

				List<ProductMaterialRecordModel> recordList = new ArrayList<>();
				Cursor cursor2 = database.rawQuery("select * from product_material_record where  product_group_id = "+product_group_id, null);
				if (cursor2 != null && cursor2.getCount() > 0) {
					while (cursor2.moveToNext()) {
						ProductMaterialRecordModel re = new ProductMaterialRecordModel();
						re.setId(cursor2.getInt(cursor2.getColumnIndex("id")));
						re.setProductGroupId(cursor2.getInt(cursor2.getColumnIndex("product_group_id")));
						re.setMaterialName(cursor2.getString(cursor2.getColumnIndex("material_name")));
						re.setMaterialType(cursor2.getInt(cursor2.getColumnIndex("material_type")));
						re.setMaterialFilepath(cursor2.getString(cursor2.getColumnIndex("material_filepath")));
						re.setMaterialFileuri(cursor2.getString(cursor2.getColumnIndex("material_fileuri")));
						re.setLocationX(cursor2.getInt(cursor2.getColumnIndex("location_x")));
						re.setLocationY(cursor2.getInt(cursor2.getColumnIndex("location_y")));
						re.setScaleRate(cursor2.getInt(cursor2.getColumnIndex("scale_rate")));
						re.setHideState(cursor2.getInt(cursor2.getColumnIndex("hide_state")));
						re.setMaterialShowState(cursor2.getInt(cursor2.getColumnIndex("material_show_state")));
						re.setMaterialKeyShowtime(cursor2.getInt(cursor2.getColumnIndex("material_key_showtime")));
						re.setKeyword(cursor2.getString(cursor2.getColumnIndex("keyword")));
						re.setAnswerTxt(cursor2.getString(cursor2.getColumnIndex("answer_txt")));
						re.setAnswerAudio(cursor2.getString(cursor2.getColumnIndex("answer_audio")));
						re.setAnswerState(cursor2.getInt(cursor2.getColumnIndex("answer_state")));
						re.setAnswerType(cursor2.getInt(cursor2.getColumnIndex("answer_type")));
						re.setCreateTime(cursor2.getString(cursor2.getColumnIndex("create_time")));
						re.setDeleteStatus(2);//删除状态 1新增 2编辑 3删除
						recordList.add(re);
					}
					cursor2.close();
				}
				model.setListRecord(recordList);

				list.add(model);
			}
			cursor1.close();
		}
		database.close();
		return list;
	}

	//查询启用中的
	@SuppressLint("Range")
	public static List<ProductGroupModel> selectAllStatusYes() {
		List<ProductGroupModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor1 = database.rawQuery("select * from product_group_list where product_group_status=1 ", null);
		if (cursor1 != null && cursor1.getCount() > 0) {
			while (cursor1.moveToNext()) {
				ProductGroupModel model = new ProductGroupModel();
				int product_group_id = cursor1.getInt(cursor1.getColumnIndex("product_group_id"));
				model.setProductGroupId(product_group_id);
				model.setProductGroupName(cursor1.getString(cursor1.getColumnIndex("product_group_name")));
				model.setProductGroupStatus(cursor1.getInt(cursor1.getColumnIndex("product_group_status")));
				model.setCreateTime(cursor1.getString(cursor1.getColumnIndex("create_time")));

				List<ProductMaterialRecordModel> recordList = new ArrayList<>();
				Cursor cursor2 = database.rawQuery("select * from product_material_record where  product_group_id = "+product_group_id, null);
				if (cursor2 != null && cursor2.getCount() > 0) {
					while (cursor2.moveToNext()) {
						ProductMaterialRecordModel re = new ProductMaterialRecordModel();
						re.setId(cursor2.getInt(cursor2.getColumnIndex("id")));
						re.setProductGroupId(cursor2.getInt(cursor2.getColumnIndex("product_group_id")));
						re.setMaterialName(cursor2.getString(cursor2.getColumnIndex("material_name")));
						re.setMaterialType(cursor2.getInt(cursor2.getColumnIndex("material_type")));
						re.setMaterialFilepath(cursor2.getString(cursor2.getColumnIndex("material_filepath")));
						re.setMaterialFileuri(cursor2.getString(cursor2.getColumnIndex("material_fileuri")));
						re.setLocationX(cursor2.getInt(cursor2.getColumnIndex("location_x")));
						re.setLocationY(cursor2.getInt(cursor2.getColumnIndex("location_y")));
						re.setScaleRate(cursor2.getInt(cursor2.getColumnIndex("scale_rate")));
						re.setHideState(cursor2.getInt(cursor2.getColumnIndex("hide_state")));
						re.setMaterialShowState(cursor2.getInt(cursor2.getColumnIndex("material_show_state")));
						re.setMaterialKeyShowtime(cursor2.getInt(cursor2.getColumnIndex("material_key_showtime")));
						re.setKeyword(cursor2.getString(cursor2.getColumnIndex("keyword")));
						re.setAnswerTxt(cursor2.getString(cursor2.getColumnIndex("answer_txt")));
						re.setAnswerAudio(cursor2.getString(cursor2.getColumnIndex("answer_audio")));
						re.setAnswerState(cursor2.getInt(cursor2.getColumnIndex("answer_state")));
						re.setAnswerType(cursor2.getInt(cursor2.getColumnIndex("answer_type")));
						re.setCreateTime(cursor2.getString(cursor2.getColumnIndex("create_time")));
						re.setDeleteStatus(2);//删除状态 1新增 2编辑 3删除
						recordList.add(re);
					}
					cursor2.close();
				}
				model.setListRecord(recordList);

				list.add(model);
			}
			cursor1.close();
		}
		database.close();
		return list;
	}

	//新增
	public static void insertData(ProductGroupModel proModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("product_group_name", proModel.getProductGroupName());
		values.put("product_group_status", 1);//启用状态 0未启用 1启用
		values.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
		long product_group_id = database.insert("product_group_list", null, values);
		values.clear();

		List<ProductMaterialRecordModel> recordList = proModel.getListRecord();
		for (ProductMaterialRecordModel rm : recordList) {
			ContentValues values2 = new ContentValues();
			values2.put("product_group_id", product_group_id);
			values2.put("material_name", rm.getMaterialName());
			values2.put("material_type", rm.getMaterialType());
			values2.put("material_filepath", rm.getMaterialFilepath());
			values2.put("material_fileuri", rm.getMaterialFileuri());
			values2.put("location_x", rm.getLocationX());
			values2.put("location_y", rm.getLocationY());
			values2.put("scale_rate", rm.getScaleRate());
			values2.put("hide_state", rm.getHideState());
			values2.put("material_show_state", rm.getMaterialShowState());
			values2.put("material_key_showtime", rm.getMaterialKeyShowtime());
			values2.put("keyword", rm.getKeyword());
			values2.put("answer_txt", rm.getAnswerTxt());
			values2.put("answer_type", rm.getAnswerType());
			values2.put("answer_state", rm.getAnswerState());
			values2.put("answer_audio", rm.getAnswerAudio());
			values2.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
			database.insert("product_material_record", null, values2);
			values2.clear();
		}
		database.close();
	}
	//更改启用状态
	@SuppressLint("Range")
	public static void updateKeywordStatus(int product_group_id, int product_group_status) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("product_group_status", product_group_status);
		database.update("product_group_list", values, "product_group_id = ?", new String[] {String.valueOf(product_group_id)});
		values.clear();
		database.close();
	}
	//更改数据
	@SuppressLint("Range")
	public static void updateData(ProductGroupModel proModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values1 = new ContentValues();
		values1.put("product_group_name", proModel.getProductGroupName());
		database.update("product_group_list", values1, "product_group_id = ?", new String[] {String.valueOf(proModel.getProductGroupId())});
		values1.clear();

		List<ProductMaterialRecordModel> recordList = proModel.getListRecord();
		for (ProductMaterialRecordModel rm : recordList) {
			//删除状态 1新增 2编辑 3删除
			ContentValues values2 = new ContentValues();
			values2.put("product_group_id", proModel.getProductGroupId());
			values2.put("material_name", rm.getMaterialName());
			values2.put("material_type", rm.getMaterialType());
			values2.put("material_filepath", rm.getMaterialFilepath());
			values2.put("material_fileuri", rm.getMaterialFileuri());
			values2.put("location_x", rm.getLocationX());
			values2.put("location_y", rm.getLocationY());
			values2.put("scale_rate", rm.getScaleRate());
			values2.put("hide_state", rm.getHideState());
			values2.put("material_show_state", rm.getMaterialShowState());
			values2.put("material_key_showtime", rm.getMaterialKeyShowtime());
			values2.put("keyword", rm.getKeyword());
			values2.put("answer_txt", rm.getAnswerTxt());
			values2.put("answer_type", rm.getAnswerType());
			values2.put("answer_state", rm.getAnswerState());
			values2.put("answer_audio", rm.getAnswerAudio());

			switch (rm.getDeleteStatus()){
				case 1://新增
					values2.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
					database.insert("product_material_record", null, values2);
					break;
				case 2://编辑
					//旧音频和新音频不一样则删除
					deleteDifferentFile(database,rm.getId(),rm.getAnswerAudio());
					//更新该记录
					database.update("product_material_record", values2, "id = ?", new String[] {String.valueOf(rm.getId())});
					break;
				case 3://删除
					//删除文件
					deleteFileOne(database,rm.getId());
					//删除该记录
					database.execSQL("delete FROM product_material_record WHERE id = "+rm.getId());
					break;
			}
			values2.clear();
		}
		database.close();
	}
	//删除产品组某条素材
	public static void deleteRecordData(int id) {
		SQLiteDatabase database = helper.getReadableDatabase();
		database.execSQL("delete FROM product_material_record WHERE id = "+id);
		deleteFileOne(database,id);
		database.close();
	}

	//删除产品组
	public static void deleteData(int product_group_id) {
		SQLiteDatabase database = helper.getReadableDatabase();
		database.execSQL("delete FROM product_group_list WHERE product_group_id = "+product_group_id);
		deleteFileALL(database,product_group_id);
		database.execSQL("delete FROM product_material_record WHERE product_group_id = "+product_group_id);
		database.close();
	}
	//先获取音频路径并且删除文件
	@SuppressLint("Range")
	public static void deleteFileOne(SQLiteDatabase database,int id){
		Cursor cursorDel = database.rawQuery("select * from product_material_record where id="+id, null);
		if (cursorDel != null && cursorDel.getCount() > 0) {
			if (cursorDel.moveToNext()) {
				String answer_audio_old = cursorDel.getString(cursorDel.getColumnIndex("answer_audio"));
				int answerType = cursorDel.getInt(cursorDel.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 2){
					//文件存在则删除
					File file = new File(answer_audio_old);
					if(file.exists()){
						file.delete();
					}
				}
			}
			cursorDel.close();
		}
	}
	//先获取音频路径并且删除文件
	@SuppressLint("Range")
	public static void deleteFileALL(SQLiteDatabase database,int product_group_id){
		Cursor cursorDel = database.rawQuery("select * from product_material_record where product_group_id="+product_group_id, null);
		if (cursorDel != null && cursorDel.getCount() > 0) {
			while (cursorDel.moveToNext()) {
				String answer_audio_old = cursorDel.getString(cursorDel.getColumnIndex("answer_audio"));
				int answerType = cursorDel.getInt(cursorDel.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 2){
					//文件存在则删除
					File file = new File(answer_audio_old);
					if(file.exists()){
						file.delete();
					}
				}
			}
			cursorDel.close();
		}
	}
	//旧音频和新音频不一样则删除
	@SuppressLint("Range")
	public static void deleteDifferentFile(SQLiteDatabase database,int id,String new_answer_audio){
		Cursor cursorEdit = database.rawQuery("select * from product_material_record where id="+id, null);
		if (cursorEdit != null && cursorEdit.getCount() > 0) {
			if (cursorEdit.moveToNext()) {
				String answer_audio_old = cursorEdit.getString(cursorEdit.getColumnIndex("answer_audio"));
				int answerType = cursorEdit.getInt(cursorEdit.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 1 || answerType == 2){
					//旧音频和新音频不一样
					if(!new_answer_audio.equals(answer_audio_old)){
						//文件存在则删除
						File file = new File(answer_audio_old);
						if(file.exists()){
							file.delete();
						}
					}
				}
			}
			cursorEdit.close();
		}
	}
}
